package com.zt.config;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

/**
 * 数据库初始化器
 * @author 周明震
 * @date 2025/7/21
 */
@Component
@Slf4j
public class DatabaseInitializer implements CommandLineRunner {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Override
    public void run(String... args) throws Exception {
        try {
            // 初始化admin_logs表
            initializeAdminLogsTable();

            // 初始化优惠券表
            initializeCouponsTable();

        } catch (Exception e) {
            log.error("数据库初始化失败", e);
        }
    }

    private void initializeAdminLogsTable() {
        try {
            // 检查admin_logs表是否存在
            String checkTableSql = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'sywl_bk' AND table_name = 'admin_logs'";
            Integer count = jdbcTemplate.queryForObject(checkTableSql, Integer.class);

            if (count == null || count == 0) {
                log.info("创建admin_logs表...");
                createAdminLogsTable();
                log.info("admin_logs表创建成功！");
            } else {
                log.info("admin_logs表已存在，跳过创建。");
            }
        } catch (Exception e) {
            log.error("初始化admin_logs表失败", e);
            throw e;
        }
    }

    private void initializeCouponsTable() {
        log.info("开始初始化优惠券表结构...");

        try {
            // 检查并添加 total_count 字段
            if (!columnExists("coupons", "total_count")) {
                jdbcTemplate.execute("ALTER TABLE coupons ADD COLUMN total_count INT(11) DEFAULT NULL COMMENT '总发放数量' AFTER u_id");
                log.info("添加 total_count 字段成功");
            } else {
                log.info("total_count 字段已存在");
            }

            // 检查并添加 limit_per_user 字段
            if (!columnExists("coupons", "limit_per_user")) {
                jdbcTemplate.execute("ALTER TABLE coupons ADD COLUMN limit_per_user INT(11) DEFAULT 1 COMMENT '每人限领数量' AFTER total_count");
                log.info("添加 limit_per_user 字段成功");
            } else {
                log.info("limit_per_user 字段已存在");
            }

            // 检查并添加 valid_hours 字段
            if (!columnExists("coupons", "valid_hours")) {
                jdbcTemplate.execute("ALTER TABLE coupons ADD COLUMN valid_hours INT(11) DEFAULT NULL COMMENT '生效时长（小时）' AFTER limit_per_user");
                log.info("添加 valid_hours 字段成功");
            } else {
                log.info("valid_hours 字段已存在");
            }

            // 检查并添加 created_at 字段
            if (!columnExists("coupons", "created_at")) {
                jdbcTemplate.execute("ALTER TABLE coupons ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' AFTER valid_hours");
                log.info("添加 created_at 字段成功");
            } else {
                log.info("created_at 字段已存在");
            }

            // 检查并添加 updated_at 字段
            if (!columnExists("coupons", "updated_at")) {
                jdbcTemplate.execute("ALTER TABLE coupons ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' AFTER created_at");
                log.info("添加 updated_at 字段成功");
            } else {
                log.info("updated_at 字段已存在");
            }

            log.info("优惠券表结构初始化完成");

        } catch (Exception e) {
            log.error("初始化优惠券表结构失败", e);
            throw e;
        }
    }

    private boolean columnExists(String tableName, String columnName) {
        try {
            String sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ? AND table_schema = DATABASE() AND column_name = ?";
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class, tableName, columnName);
            return count != null && count > 0;
        } catch (Exception e) {
            log.error("检查字段是否存在时出错: {}.{}", tableName, columnName, e);
            return false;
        }
    }
    
    private void createAdminLogsTable() {
        String createTableSql = """
            CREATE TABLE `admin_logs` (
              `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
              `admin_id` int(11) DEFAULT NULL COMMENT '管理员ID',
              `admin_username` varchar(50) DEFAULT NULL COMMENT '管理员用户名',
              `module` varchar(50) DEFAULT NULL COMMENT '操作模块',
              `operation` varchar(50) DEFAULT NULL COMMENT '操作类型',
              `description` varchar(200) DEFAULT NULL COMMENT '操作描述',
              `method` varchar(10) DEFAULT NULL COMMENT '请求方法',
              `url` varchar(200) DEFAULT NULL COMMENT '请求URL',
              `params` text COMMENT '请求参数',
              `result` text COMMENT '响应结果',
              `status` tinyint(1) DEFAULT '1' COMMENT '操作状态：1-成功，0-失败',
              `error_msg` text COMMENT '错误信息',
              `ip` varchar(50) DEFAULT NULL COMMENT '客户端IP',
              `user_agent` varchar(500) DEFAULT NULL COMMENT '用户代理',
              `execute_time` bigint(20) DEFAULT NULL COMMENT '执行时间（毫秒）',
              `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
              PRIMARY KEY (`id`),
              KEY `idx_admin_id` (`admin_id`),
              KEY `idx_module` (`module`),
              KEY `idx_operation` (`operation`),
              KEY `idx_status` (`status`),
              KEY `idx_created_at` (`created_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员操作日志表'
            """;
        
        jdbcTemplate.execute(createTableSql);
        
        // 插入示例数据
        String insertSampleData = """
            INSERT INTO `admin_logs` (`admin_id`, `admin_username`, `module`, `operation`, `description`, `method`, `url`, `params`, `result`, `status`, `error_msg`, `ip`, `user_agent`, `execute_time`, `created_at`) VALUES
            (11, 'admin', '系统管理', '登录', '管理员登录系统', 'POST', '/api/admin/login', '[{"username":"admin","password":"******"}]', 'code:200, msg:登录成功', 1, NULL, '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36', 156, '2025-01-21 10:30:00'),
            (11, 'admin', '教师管理', '审核通过', '审核通过教师认证申请', 'PUT', '/teacher/review/1/approve', '[1]', 'code:200, msg:审核成功', 1, NULL, '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36', 89, '2025-01-21 10:35:00'),
            (11, 'admin', '科目管理', '新增', '新增科目', 'POST', '/subject/add', '[{"name":"数学","description":"数学科目"}]', 'code:200, msg:新增成功', 1, NULL, '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36', 67, '2025-01-21 10:40:00'),
            (11, 'admin', '科目管理', '删除', '删除科目', 'POST', '/subject/delete/5', '[5]', 'code:200, msg:删除成功', 1, NULL, '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36', 45, '2025-01-21 10:45:00'),
            (11, 'admin', '教师管理', '审核拒绝', '审核拒绝教师认证申请', 'PUT', '/teacher/review/2/reject', '[2, {"reason":"资料不完整"}]', 'code:200, msg:审核成功', 1, NULL, '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36', 78, '2025-01-21 10:50:00')
            """;
        
        jdbcTemplate.execute(insertSampleData);
    }
}
